package com.aquila.datacontrol.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.aquila.datacontrol.object.CNBibleObject;

public class CNBibleReadSqlite extends SQLiteOpenHelper {
	public static final String TABLE_NAME = "cn_holybible";
	public final static String dbName = "CN_bible_data.db";

	private Context context ;
	public static final int Version =1;
	private SQLiteDatabase db;
	
	private final String KEY_id = "id" ;
	private final String KEY_bookName = "bookName" ;
	private final String KEY_shortBookName = "shortBookName" ;
	private final String KEY_enBookName = "enBookName" ;

	private final String KEY_enShortBookName = "enShortBookName" ;
	private final String KEY_bookIndex = "bookIndex";
	private final String KEY_chapterIndex = "chapterIndex";
	private final String KEY_chapterCount = "chapterCount";
	private final String KEY_sectionText = "sectionText";
	private final String KEY_nivSectionText = "nivSectionText";
	private final String KEY_sectionId = "sectionId";
	private final String KEY_sectionIndex = "sectionIndex";
	private final String KEY_sectionCount = "sectionCount";
	private final String KEY_isTitle = "isTitle";
	private final String KEY_isNew = "isNew";
	private final String KEY_firstFlag = "firstFlag";
	private final String KEY_secontFlag = "secontFlag";
	private final String KEY_belongTo = "belongTo";
	
	public CNBibleReadSqlite(Context context) {
		super(context, dbName, null, Version) ;
		this.context = context ;
		db = getWritableDatabase() ;
		createTable(db) ;
		
	}
	
	private void createTable(SQLiteDatabase databasei) {
		String sqlstr = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ( "+
				KEY_id+" INTEGER PRIMARY KEY, "+
				KEY_isNew + " INTEGER, "+
	            KEY_bookName + "  VARCHAR2(30), "+
	            KEY_enBookName+ "  VARCHAR2(50), "+
	            KEY_shortBookName + "  VARCHAR2(30), "+
	            KEY_enShortBookName + "  VARCHAR2(30), "+
	            KEY_belongTo + "  VARCHAR2(30), "+
	            KEY_bookIndex + "  INTEGER, "+
	            KEY_chapterIndex + "  INTEGER, "+
	            KEY_chapterCount + "  INTEGER, "+
	            KEY_sectionIndex + " INTEGER, " +
	            KEY_sectionText +" TEXT, "+
	            KEY_sectionId + " INTEGER, " +
	            KEY_sectionCount+ " INTEGER," + 
	            KEY_isTitle+ " INTEGER, " +
	            KEY_nivSectionText + " TEXT, " +			
	            KEY_firstFlag + " TEXT, " +
	            KEY_secontFlag+ " TEXT)" ;
		try{
			db.execSQL(sqlstr);
			}catch (SQLException e) {
				e.printStackTrace() ;
			}
    }
	
	
	public String readTitle(){
		String sql = "select * from " + TABLE_NAME + " where "+KEY_isTitle +"  =  '1'" ;
		Cursor cursor = db.rawQuery(sql, null) ;
		if (cursor == null) {
        	return "无数据" ;
        }
		cursor.moveToFirst() ;
		System.out.println(cursor.getCount());
		
		int i = 0 ;
		StringBuffer sb = new StringBuffer() ;
		String str ;
		while(cursor.moveToNext()){
//		if (i < 10) {
					str = cursor.getString(cursor.getColumnIndex(KEY_shortBookName)) +"" +
					cursor.getInt(cursor.getColumnIndex(KEY_chapterIndex)) + ":"+
					cursor.getInt(cursor.getColumnIndex(KEY_sectionId)) +" " +
					cursor.getString(cursor.getColumnIndex(KEY_sectionText))+"@@@@" ; 
			System.out.println(str);
        }
		cursor.close() ;
		return sb.toString() ;
	}
	
	
	
	/**
	 * @param object
	 *Administrator
	 *2012-11-9 下午10:40:31
	 */
	private ContentValues writeDataToCursor(CNBibleObject object){
		ContentValues cv = new ContentValues() ;
		cv.put(KEY_belongTo ,  object.getBelongTo() ) ;
		cv.put(KEY_bookIndex ,  object.getBookIndex() ) ;
		cv.put(KEY_bookName ,  object.getEnBookName() ) ;
		cv.put(KEY_chapterCount ,  object.getChapterCount() ) ;
		cv.put(KEY_chapterIndex ,  object.getChapterIndex() ) ;
		cv.put(KEY_enBookName ,  object.getEnBookName() ) ;
		cv.put(KEY_nivSectionText ,  object.getEnSectionText() ) ;
		cv.put(KEY_enShortBookName ,  object.getEnShortBookName() ) ;
		cv.put(KEY_firstFlag ,  object.getFirstFlag() ) ;
		cv.put(KEY_id ,  object.getId() ) ;
		cv.put(KEY_isNew ,  object.getIsNew() ) ;
		cv.put(KEY_isTitle ,  object.getIsTitle() ) ;
		cv.put(KEY_secontFlag ,  object.getSecontFlag() ) ;
		cv.put(KEY_sectionCount ,  object.getSectionCount()) ;
		cv.put(KEY_sectionId ,  object.getSectionId() ) ;
		cv.put(KEY_sectionIndex ,  object.getSectionIndex() ) ;
		cv.put(KEY_sectionText ,  object.getSectionText() ) ;
		cv.put(KEY_shortBookName ,  object.getShortBookName() ) ;
		return cv ;
	}
	
	
	/**
	 * 读取数据
	 * @param cursor
	 * @return
	 *Administrator
	 *2012-11-9 下午10:39:34
	 */
	private CNBibleObject readDataToObject(Cursor cursor){
		CNBibleObject object = new CNBibleObject() ;
		object.setBelongTo(cursor.getString(cursor.getColumnIndex(KEY_belongTo))) ;
		object.setBookIndex(cursor.getInt(cursor.getColumnIndex(KEY_bookIndex))) ;
		object.setBookName( cursor.getString(cursor.getColumnIndex(KEY_bookName))) ;
		object.setChapterCount( cursor.getInt(cursor.getColumnIndex(KEY_chapterCount))) ;
		object.setChapterIndex( cursor.getInt(cursor.getColumnIndex(KEY_chapterIndex))) ;
		object.setEnBookName( cursor.getString(cursor.getColumnIndex(KEY_enBookName))) ;
		object.setEnSectionText(cursor.getString(cursor.getColumnIndex(KEY_nivSectionText))) ;
		object.setEnShortBookName( cursor.getString(cursor.getColumnIndex(KEY_enShortBookName))) ;
		object.setFirstFlag( cursor.getString(cursor.getColumnIndex(KEY_firstFlag))) ;
		object.setId( cursor.getInt(cursor.getColumnIndex(KEY_id))) ;
		object.setIsNew( cursor.getInt(cursor.getColumnIndex(KEY_isNew))) ;
		object.setIsTitle( cursor.getInt(cursor.getColumnIndex(KEY_isTitle))) ;
		object.setSecontFlag( cursor.getString(cursor.getColumnIndex(KEY_secontFlag))) ;
		object.setSectionCount( cursor.getInt(cursor.getColumnIndex(KEY_sectionCount))) ;
		object.setSectionId( cursor.getInt(cursor.getColumnIndex(KEY_sectionId))) ;
		object.setSectionIndex( cursor.getInt(cursor.getColumnIndex(KEY_sectionIndex))) ;
		object.setSectionText( cursor.getString(cursor.getColumnIndex(KEY_sectionText))) ;
		object.setShortBookName(cursor.getString(cursor.getColumnIndex(KEY_shortBookName))) ;
		
		return object ;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	@Override
    public void onCreate(SQLiteDatabase db) {
		
    }

	@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	    // TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(db);
    }
	/***
	 * 打开数据库
	 *2012-7-30 下午1:37:13
	 */
	public void open() {
		if (db == null || !db.isOpen()) {
			db = getWritableDatabase() ;
		}
	}
	
	/**
	 * 关闭数据库
	 */
	public void close(){
		if (db.isOpen()) {
			db.close() ;
		}
	}
		
	
	
	
	
	
	
	
	
	
	
	
	
	
}
